package com.cennavi.mineplay.mineplay_data.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLTimeoutException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.jboss.logging.Logger;

import com.alibaba.fastjson.JSONObject;
import com.cennavi.mineplay.mineplay_data.model.db.inter.DBConnection;
import com.cennavi.mineplay.mineplay_data.model.transform.inter.Gps;
import com.cennavi.mineplay.mineplay_data.model.transform.inter.PositionUtil;
import com.cennavi.mineplay.mineplay_data.model.transform.inter.Transform;
import com.cennavi.mineplay.mineplay_data.model.transform.inter.TransformImpl;
import com.cennavi.mineplay.mineplay_data.utils.EventMsgTransfromTool;

public class DBTask extends Thread{
	
	private static Logger logger = Logger.getLogger(DBTask.class);

	private DBConnection dbconn;
	
	private DBConnectionInfo dbinfo;
	
	private DbNameInfo dbNameInfo;
	
	private TableNameInfo tableNameInfo;
	
	private List<Transform> trans;
	
	private String destDBJdbc;
	
	private String dbDriverClass;
	
	private String username;
	
	private String password;
	
	private volatile boolean is_stop = false;
	
	private String destTableNames;
	
	public boolean isIs_stop() {
		return is_stop;
	}



	public void setIs_stop(boolean is_stop) {
		this.is_stop = is_stop;
	}



	public DBTask(DBConnection dbconn, DBConnectionInfo dbinfo,
			DbNameInfo dbNameInfo, TableNameInfo tableNameInfo,
			List<Transform> trans, String destDBJdbc, String dbDriverClass,
			String username, String password,String destTableNames) {
		super();
		this.dbconn = dbconn;
		this.dbinfo = dbinfo;
		this.dbNameInfo = dbNameInfo;
		this.tableNameInfo = tableNameInfo;
		this.trans = trans;
		this.destDBJdbc = destDBJdbc;
		this.dbDriverClass = dbDriverClass;
		this.username = username;
		this.password = password;
		this.destTableNames = destTableNames;
		
		this.setName(dbinfo.getJdbc());
	}

	
	private Connection getDestConnection() throws Exception{
		Class.forName(this.dbDriverClass);
		Connection conn = DriverManager.getConnection(destDBJdbc, username, password);
		return conn;
	}
	
	
	private List<TrackPoint> generateTps(Connection conn,String queryData,int company_id) throws SQLException{
		
		boolean flag = true;
		
		List<TrackPoint> tps = new ArrayList<TrackPoint>();
		
		while(flag){
			
			Statement stmtData = conn.createStatement();
			
			stmtData.setQueryTimeout(20);
			
			
			try {
				logger.info("db start download track data......");
				
				ResultSet rsData = stmtData.executeQuery(queryData);
				
				rsData.setFetchSize(3000);
		
				boolean isfirst = true;
				
				while(rsData.next()){
					
					String car_number = rsData.getString("car_number");
					double lng = rsData.getDouble("lng");
					double lat = rsData.getDouble("lat");
					double speed = rsData.getDouble("speed");
					double direct = rsData.getDouble("direct");
					long time = Long.parseLong(rsData.getString("time"));
					
					
					String is_online_str = rsData.getString("is_online");
					
					int is_online = 0;
					
					if ("1".equals(is_online_str)){
						is_online = 1;
					}else if ("ACC开".equals(is_online_str)){
						is_online = 1;
					}
					
					TrackPoint tp = new TrackPoint(car_number, company_id, lng, lat, speed, direct, time,is_online);
					
					for(Transform tran : trans){
						tran.trans(tp);
					}
//				logger.info(1);
					if (lng !=0)
					tps.add(tp);
					
					if (isfirst) {
						logger.info("db start downloading track data......");
						isfirst = false;
					}
				}
				
				flag = false;
				
				rsData.close();
			} catch (SQLTimeoutException e) {
				logger.info("query timeout......"+queryData);
			}
			stmtData.close();
			logger.info("db finish download track data......");
			
		}
		
		
		return tps;
	}
	private Pattern pattern = Pattern.compile("[0-9]*");
	private boolean isNumeric(String str){
        
        Matcher isNum = pattern.matcher(str);
        if( !isNum.matches() ){
            return false;
        }
        return true;
  }
	
	private List<EventPoint> generateEps(Connection conn,String queryData) throws Exception{
		
		
		boolean flag = true;
		
		List<EventPoint> tps = new ArrayList<EventPoint>();
		
		while(flag){
			Statement stmtData = conn.createStatement();
			
			stmtData.setQueryTimeout(20);
			
			logger.info("db start download event data......");
			
			ResultSet rsData;
			try {
				rsData = stmtData.executeQuery(queryData);
				
				rsData.setFetchSize(3000);
			
				boolean isfirst = true;
				
				while(rsData.next()){
					
					String car_number = rsData.getString("car_number");
					double lng = rsData.getDouble("lng");
					double lat = rsData.getDouble("lat");
					
					String typeStr = rsData.getString("type");
					
					long type = 0;
					
					if (!this.isNumeric(typeStr)){
						//进行转换
						type =  EventMsgTransfromTool.eventMsgTransform(typeStr);
					}else{
						type = Long.parseLong(typeStr);
					}
					
//				long type = Long.parseLong(rsData.getString("type"));
					
					long time = Long.parseLong(rsData.getString("time"));
					
					double speed = rsData.getDouble("speed");
					
					EventPoint tp = new EventPoint(car_number,   lng, lat, time,type,speed);
					
					for(Transform tran : trans){
						if (tran instanceof TransformImpl){
							Gps gcj02GPSCoordinate = PositionUtil.gps84_To_Gcj02(lng, lat);
							
							tp.setLng(gcj02GPSCoordinate.getWgLon());
							tp.setLat(gcj02GPSCoordinate.getWgLat());
							
						}
					}
					
					if (lng !=0 && type >0)
					 tps.add(tp);
					
					if (isfirst) {
						logger.info("db start downloading event data......");
						isfirst = false;
					}
				}
				
				rsData.close();
				
				flag = false;
			} catch (SQLTimeoutException e) {
				logger.info("event query timeout......"+queryData);
			}
			
			stmtData.close();
			
		}
		
		
		
		logger.info("db finish download event data......");
		return tps;
	}
	
	


	public void run(){
		
		int company_id = this.dbinfo.getCompany_id();
		
		
		
		
		
		//
		while(!is_stop){
			
			List<Connection> listConn = new ArrayList<Connection>();
			
			try {
				Connection destConn = null;
//			try {
					destConn = this.getDestConnection();
					
					listConn.add(destConn);
//			} catch (Exception e1) {
//				 
//				e1.printStackTrace();
//			}
				
				
				Connection[] conns = dbconn.getConnnection();
				
				boolean isAllNull = true;
				
				for(Connection conn: conns){
					if (conn != null)
					{
						isAllNull = false;
						break;
					}
				}
				
				if (isAllNull){
					logger.info("all connection is null"+ dbconn.getSql());
				}
				
				logger.info("db connection finish");
				
				for(Connection conn: conns){
					
					if (conn == null) continue;
					
					listConn.add(conn);
					
					String[] sqls = this.dbinfo.getSql().split(";");
					
//				String sql = this.dbinfo.getSql();
					
					String sql = sqls[0];
					
					String sqlEvent = sqls[1];
					
					if (sql.indexOf(" from ")>0 && sql.indexOf("?")<0){
						List<TrackPoint> tps;
//					try {
							if (sql.indexOf("where")>0){
								tps = this.generateTps(conn, sql +" and "+ this.dbconn.getSql(), company_id);
							}else{
								tps = this.generateTps(conn, sql +" where "+ this.dbconn.getSql(), company_id);
							}
							logger.info("1-"+sql +" where "+ this.dbconn.getSql());
							logger.info("1-"+"execute import data size:"+tps.size());
						
							ImportTrackPoint importTrackPoint = new ImportTrackPoint(destConn, tps, destTableNames);
							importTrackPoint.setSourceConnection(conn);
							logger.info("db start import track data......");
							importTrackPoint.importData();
							logger.info("db finish import track data......");
//					} catch (Exception e) {
//						logger.info("myexception:"+e.getMessage());
//						e.printStackTrace();
//					}
					}else{
						if (tableNameInfo.getTableNames().size()==1){
							
							//String queryData = sql + " from "+ tableNameInfo.getTableNames().get(0) +" where "+ this.dbconn.getSql();
							String queryData = sql;
							if (sql.indexOf("where")>0){
								queryData += " and " + this.dbconn.getSql();
							}else{
								queryData += " where " + this.dbconn.getSql();
							}
							List<TrackPoint> tps;
//						try {
								tps = this.generateTps(conn, queryData, company_id);
								logger.info("2-"+queryData);
								logger.info("2-"+"execute import data size:"+tps.size());
								ImportTrackPoint importTrackPoint = new ImportTrackPoint(destConn, tps, destTableNames);
								importTrackPoint.setSourceConnection(conn);
								logger.info("db start import track data......");
								importTrackPoint.importData();
								logger.info("db finish import track data......");
//						} catch (Exception e) {
//							logger.info("myexception:"+e.getMessage());
//							e.printStackTrace();
//						}
							
						}else{
							String tabPrefix = tableNameInfo.getTableNames().get(1);
							
							String dbtype = this.dbinfo.getDbType();
							
							if ("sqlserver".equals(dbtype)){
								String query = "SELECT distinct table_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME like '"+tabPrefix+"%'";
							
								List<String> tablenames = new ArrayList<String>();
								
//							try {
									Statement stmt = conn.createStatement();
									
									ResultSet rs = stmt.executeQuery(query);
									
									while(rs.next())
										tablenames.add(rs.getString(1));
									
									rs.close();
									
									stmt.close();
									
									for(String tabname : tablenames){
										String queryData = sql.replace("?", tabname);
										 
										if (sqls.length == 2) {
											if (sql.indexOf(" where ") > 0) {
												queryData += " and "
														+ this.dbconn.getSql();
											} else {
												queryData += " where "
														+ this.dbconn.getSql();
											}
										}
										
										
//									String queryData = sql + " from "+ tabname +" where "+ this.dbconn.getSql();
										 
//									logger.info(queryData);
										
										List<TrackPoint> tps = this.generateTps(conn, queryData, company_id);
										logger.info("3-"+queryData);
										logger.info("3-"+"execute import data size:"+tps.size());
										
										ImportTrackPoint importTrackPoint = new ImportTrackPoint(destConn, tps, destTableNames);
										importTrackPoint.setSourceConnection(conn);
//									try {
											importTrackPoint.importData();
//									} catch (Exception e) {
//										logger.info("myexception:"+e.getMessage());
//										e.printStackTrace();
//									}
									}
									
									
									
//							} catch (SQLException e) {
//								 
//								e.printStackTrace();
//							}
							}
						}
					}
					
					
					//导入事件
//				try{
						
						if (sqlEvent.indexOf("年月")>0){
							sqlEvent = sqlEvent.replace("年月", new SimpleDateFormat("yyyyMM").format(new Date()));
						}
						
						
						
						if (sqlEvent.indexOf("where")>0){
							sqlEvent += " and " + this.dbconn.getSql();
						}else{
							sqlEvent += " where " + this.dbconn.getSql();
						}
						
						List<EventPoint> eps = this.generateEps(conn, sqlEvent);
						
						ImportEventPoint importEventPoint = new ImportEventPoint(destConn, eps, "alarm");
						logger.info("db start import event data......");
						importEventPoint.importData();
						logger.info("db finish import event data......");
						
						
//				}catch(Exception e){
//					logger.info("myexception:"+e.getMessage());
//					e.printStackTrace();
//				}
					
					
//				try {
						conn.close();
//				} catch (SQLException e) {
//					logger.info("myexception:"+e.getMessage());
//					e.printStackTrace();
//				}
				}
				
//			try {
					destConn.close();
//			} catch (SQLException e) {
//				logger.info("myexception:"+e.getMessage());
//				e.printStackTrace();
//			}
				
				logger.info("...finish one round...");
				
//			try {
					Thread.sleep(5000);
//			} catch (InterruptedException e) {
//				e.printStackTrace();
//			}
			}  catch (Exception e) {
				
				StringBuilder sb = new StringBuilder();
				
				 StackTraceElement elements[] = e.getStackTrace();
			        for (int i = 0; i < elements.length; i++) {
			            StackTraceElement stackTraceElement=elements[i];
			            String className=stackTraceElement.getClassName();
			            String methodName=stackTraceElement.getMethodName();
			            String fileName=stackTraceElement.getFileName();
			            int lineNumber=stackTraceElement.getLineNumber();
			           sb.append("StackTraceElement=>i="+i+",fileName="
			                    +fileName+",className="+className+",methodName="+methodName+",lineNumber="+lineNumber);
			           sb.append("\n");
			        }
				
				logger.info("total exception: "+e.getMessage()+"-"+sb.toString());
				e.printStackTrace();
			}finally {
				for(Connection conn:listConn){
					try {
						if (conn != null && !conn.isClosed()){
							conn.close();
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
		}
		
	}
	
	
	
//	public static void main(String[] args) throws Exception {
//		
////		Class.forName("org.postgresql.Driver");
////		
////		Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/lilei_db1", "postgres", "lilei");
////		
////		String sql = "insert into test2 values (?,?)";
////		
////		PreparedStatement pstmt = conn.prepareStatement(sql);
////		
////		for(int i=0;i<100;i++){
////			pstmt.setInt(1, i);
////			pstmt.setInt(2, i * i);
////			pstmt.addBatch();
////		}
////		
////		pstmt.executeBatch();
//		
//		
////		TrackPoint tp = new TrackPoint("京289GW", 123, 116.12345, 39.12345, 76.32, 45.22, 12343432);
////		
////		logger.info(com.alibaba.fastjson.JSONObject.toJSON(tp));
//		
//		
//		CloseableHttpClient httpClient = HttpClients.createDefault();
//        //创建一个uri对象
//        URIBuilder uriBuilder = new URIBuilder("http://124.152.228.173:4015/WisdomGPSApi/RegulatoryPlatform/GetTaxiDynamicData?startTime=20180626120000&endTime=20180626130000");
//        //uriBuilder.addParameter("query", "花千骨");
//        HttpGet get = new HttpGet(uriBuilder.build());
//        CloseableHttpResponse response = httpClient.execute(get);
//        //取响应的结果
////        int statusCode = response.getStatusLine().getStatusCode();
////        logger.info(statusCode);
//        HttpEntity entity = response.getEntity();
//        String string = EntityUtils.toString(entity, "utf-8");
//        logger.info(string); 
//	}
}
